package org.example.Utils;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;

import org.apache.commons.compress.utils.Lists;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.persistence.model.reportSub;

public class ReadExcelExample {
    public static void main(String[] args) {
        String filePath = "C:\\Users\\12406\\Desktop\\123456.xlsx";
        List<reportSub> reportSubs = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(1);  // 选择Excel文件中的第一个工作表
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) { // 逐行遍历工作表
                Row row = rowIterator.next();
                if (row.getRowNum() == 0) continue; // 跳过表头
                reportSub reportSub = new reportSub();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) { // 逐列读取单元格内容并赋值给 Person 对象
                    Cell cell = cellIterator.next();
                    int columnIndex = cell.getColumnIndex();
                    switch (columnIndex) {
                        case 0:
                            reportSub.setSubUserName(cell.getStringCellValue());
                            break;
                        case 1:
                            reportSub.setSubUserCode(cell.getStringCellValue());
                            break;
                        default:
                            break;
                    }
                }
                reportSubs.add(reportSub); // 将 Person 对象添加到列表中
            }
            System.out.println(reportSubs); // 输出所有 Person 对象
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<String> dataList = Lists.newArrayList();
      String a;
        for (reportSub reportSub : reportSubs) {
            String s1 = UUID.randomUUID().toString().replaceAll("-","");
            a="insert into ccdss_report_sub"+
                    "(id,report_num,sub_user_code,sub_user_name,create_user_code,create_user_name,create_time)"
                    +"values('"+s1+"','e6bba088f42545379444bc718c159197','"+reportSub.getSubUserCode()+"','"+reportSub.getSubUserName()
            +"','admin',"+"'超级管理员',"+"now());";
            dataList.add(a);
        }

        String filePathshufu = "C:\\Users\\12406\\Desktop\\test1.txt";

        try (FileWriter writer = new FileWriter(filePathshufu)) {
            for (String data : dataList) {
                writer.write(data + System.lineSeparator()); // 写入每个字符串，并在其后面加上一个换行符
            }
            System.out.println(dataList.size());
            System.out.println("数据已成功写入到文件 " + filePathshufu);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void test1(){
        String filePath = "C:\\Users\\12406\\Desktop\\123456.xlsx";
        List<reportSub> reportSubs = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);  // 选择Excel文件中的第一个工作表
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) { // 逐行遍历工作表
                Row row = rowIterator.next();
                if (row.getRowNum() == 0) continue; // 跳过表头
                reportSub reportSub = new reportSub();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) { // 逐列读取单元格内容并赋值给 Person 对象
                    Cell cell = cellIterator.next();
                    int columnIndex = cell.getColumnIndex();
                    switch (columnIndex) {
                        case 0:
                            reportSub.setSubUserName(cell.getStringCellValue());
                            break;
                        case 1:
                            reportSub.setSubUserCode(cell.getStringCellValue());
                            break;
                        default:
                            break;
                    }
                }
                reportSubs.add(reportSub); // 将 Person 对象添加到列表中
            }
            System.out.println(reportSubs); // 输出所有 Person 对象
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<String> dataList = Lists.newArrayList();
        String a;
        for (reportSub reportSub : reportSubs) {
            String s1 = UUID.randomUUID().toString().replaceAll("-","");
            a="insert into ccdss_report_sub"+
                    "(id,report_num,sub_user_code,sub_user_name,create_user_code,create_user_name,create_time)"
                    +"values('"+s1+"','e6bba088f42545379444bc718c159197','"+reportSub.getSubUserCode()+"','"+reportSub.getSubUserName()
                    +"','admin',"+"'超级管理员',"+"now());";
            dataList.add(a);
        }

        String filePathshufu = "C:\\Users\\12406\\Desktop\\test1.txt";

        try (FileWriter writer = new FileWriter(filePathshufu)) {
            for (String data : dataList) {
                writer.write(data + System.lineSeparator()); // 写入每个字符串，并在其后面加上一个换行符
            }
            System.out.println(dataList.size());
            System.out.println("数据已成功写入到文件 " + filePathshufu);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void test2(){
//        String filePath = "C:\\Users\\12406\\Desktop\\test.xlsx";
//        List<reportSub> list = Lists.newArrayList();
//        try (FileInputStream fis = new FileInputStream(filePath);
//             Workbook workbook = new XSSFWorkbook(fis)) {
//            Sheet sheet = workbook.getSheetAt(0);  // 选择Excel文件中的第一个工作表
//            reportSub reportSub = null;
//            for (Row row : sheet) {  // 遍历所有行
//                for (Cell cell : row) { // 遍历当前行的所有单元格
//                    reportSub = new reportSub();
//                    if (cell.getCellType() == CellType.NUMERIC) { // 检查单元格类型是否为数值
//                        DecimalFormat df = new DecimalFormat("0"); // 用 DecimalFormat 格式化数值
//                        System.out.print(df.format(cell.getNumericCellValue()) + "\t");
//                    } else if (cell.getCellType() == CellType.STRING) { // 检查单元格类型是否为字符串
//                        reportSub.setSubUserName(cell.getStringCellValue());
//                        System.out.print(cell.getStringCellValue() + "\t");
//                    } else if (cell.getCellType() == CellType.BLANK) { // 检查单元格类型是否为空白单元格
//                        System.out.print("\t"); // 如果是空单元格则什么都不输出
//                    }
//                    list.add(reportSub);
//                }
//                System.out.println();
//            }
//            System.out.println(list.size());
//            System.out.println(list);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
    }

    private static void test3(){
        String filePath = "C:\\Users\\12406\\Desktop\\work.xlsx";
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);  // 选择Excel文件中的第一个工作表
            for (Row row : sheet) {  // 遍历所有行
                for (Cell cell : row) { // 遍历当前行的所有单元格
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + "\t");
                }
                System.out.println();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
